<?php

namespace Lib\Model;

class PostModel {
    
    const STATUS_OK = 0;
    const STATUS_DELETED = 4;

    static function getById($id) {
        //$cache = \Lib\Registry::get('cache');
        //$key = "post_{$id}";
        //$result = $cache->load($key, 30);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en,
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join user u on u.id = p.user_id
            where p.id = :id
            and p.status = :status
            limit 1");
            $b->bindValue("id", $id, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetch(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function getByCategoryId($categoryId, $offset = 0, $limit = 10) {
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsByCategory_{$categoryId}_{$offset}_{$limit}";
        //$result = $cache->load($key, 30);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join user u on u.id = p.user_id
            where p.category_id = :categoryId
            and p.status = :status
            limit :offset, :limit");
            $b->bindValue("categoryId", $categoryId, \PDO::PARAM_INT);
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function getByUserId($userName, $offset = 0, $limit = 10) {
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsByUser_{$userName}_{$offset}_{$limit}";
        //$result = $cache->load($key, 30);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join user u on u.id = p.user_id
            where u.username = :userName
            and p.status = :status
            limit :offset, :limit");
            $b->bindValue("userName", $userName, \PDO::PARAM_INT);
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function getTop($offset = 0, $limit = 10) {
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsTop_{$offset}_{$limit}";
        //$result = $cache->load($key, 30);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join user u on u.id = p.user_id
            left join post_views v on v.post_id = p.id 
            where 1 
            and p.status = :status
            order by v.views desc
            limit :offset, :limit");
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function getLatest($offset = 0, $limit = 10) {
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsTop_{$offset}_{$limit}";
        //$result = $cache->load($key, 30);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join user u on u.id = p.user_id
            left join post_views v on v.post_id = p.id 
            where 1 
            and p.status = :status
            order by p.atUpdated desc
            limit :offset, :limit");
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function getByTag($tag, $offset = 0, $limit = 10) {
        $tag = strtolower($tag);
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsTag_{$tag}_{$offset}_{$limit}";
        //$result = $cache->load($key, 300);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join post_tags pt on pt.post_id = p.id
            join tags t on t.id = pt.tag_id and t.tag = :tag
            join user u on u.id = p.user_id
            left join post_views v on v.post_id = p.id 
            where 1 
            and p.status = :status
            order by v.views desc
            limit :offset, :limit");
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->bindValue("tag", $tag, \PDO::PARAM_STR);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function search($q, $offset = 0, $limit = 10) {
        $q = strtolower($q);
        //$cache = \Lib\Registry::get('cache');
        //$key = "postsTag_{$tag}_{$offset}_{$limit}";
        //$result = $cache->load($key, 300);
        //if (!$result) {
            $b = \Lib\Database::getConnection()->prepare("
            select p.id, p.title, p.post, p.description, c.name_" . \Lib\Registry::get("trans")->getLocale() . " as category, c.name_en as category_en, 
            u.username as user, p.atUpdated as date 
            from post p
            join category c on c.id = p.category_id
            join post_tags pt on pt.post_id = p.id
            join tags t on t.id = pt.tag_id
            join user u on u.id = p.user_id
            left join post_views v on v.post_id = p.id 
            where 1 
            and p.status = :status
            and p.title like '%{$q}%'
            order by v.views desc
            limit :offset, :limit");
            $b->bindValue("offset", $offset, \PDO::PARAM_INT);
            $b->bindValue("limit", $limit, \PDO::PARAM_INT);
            $b->bindValue("status", self::STATUS_OK, \PDO::PARAM_INT);
            $b->execute();
            $result = $b->fetchAll(\PDO::FETCH_OBJ);
            //$cache->save($result, $key);
        //}
        return $result;
    }
    
    static function add($category = NULL, $title = NULL, $text = NULL, $description = '') {

        if (!$category || !$title || !$text) {
            return false;
        }

        $user = \Lib\Model\UserModel::getByName(\Lib\Model\UserModel::getUser());
        if (!isset($user['id'])) {
            return false;
        }

        $category = \Lib\Model\CategoryModel::getByName($category);
        if (!isset($category['id'])) {
            return false;
        }

        try {
            $b = \Lib\Database::getConnection()->prepare("
            insert into post(user_id, title, post,description, category_id, status) values (:user_id, :title, :post, :description, :category_id, 0);");
            $b->bindValue("user_id", $user['id'], \PDO::PARAM_INT);
            $b->bindValue("title", $title, \PDO::PARAM_STR);
            $b->bindValue("post", $text, \PDO::PARAM_STR);
            $b->bindValue("description", $description, \PDO::PARAM_STR);
            $b->bindValue("category_id", $category['id'], \PDO::PARAM_INT);
            $b->execute();
            $postId = \Lib\Database::getLastInsertId();
        } catch (Exception $e) {
            return false;
        }
        return $postId;
    }
    
    static function update($postId, $category = NULL, $title = NULL, $text = NULL, $description = '', $status = 0) {

        if (!$postId || !$category || !$title || !$text) {
            return false;
        }

        $user = \Lib\Model\UserModel::getByName(\Lib\Model\UserModel::getUser());
        if (!isset($user['id'])) {
            return false;
        }

        $category = \Lib\Model\CategoryModel::getByName($category);
        if (!isset($category['id'])) {
            return false;
        }

        try {
            $b = \Lib\Database::getConnection()->prepare("
            update post set title = :title, post = :post, category_id = :category_id, description = :description, status = :status where id = :post_id;");
            $b->bindValue("title", $title, \PDO::PARAM_STR);
            $b->bindValue("post", $text, \PDO::PARAM_STR);
            $b->bindValue("description", $description, \PDO::PARAM_STR);
            $b->bindValue("category_id", $category['id'], \PDO::PARAM_INT);
            $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
            $b->bindValue("status", $status, \PDO::PARAM_INT);
            $b->execute();
        } catch (Exception $e) {
            echo $e->getMessage();
            return false;
        }
        
        //$cache = \Lib\Registry::get('cache');
        //$key = "post_{$postId}";
        //$cache->delete($key);
        
        //$key = "categories_not_empty";
        //$cache->delete($key);
        
        return $postId;
    }
    
    static function setView($postId){
        if (!$postId) {
            return false;
        }
        
        try {
            $sql = "insert
            into post_views(post_id, views)
            values(:post_id, 1)
            ON DUPLICATE KEY UPDATE views = views + 1";
            $b = \Lib\Database::getConnection()->prepare($sql);
            $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
            $b->execute();
        } catch (Exception $e) {
            echo $e->getMessage();
            return false;
        }
        
        //$cache = \Lib\Registry::get('cache');
        //$key = "post_views_{$postId}";
        //$cache->delete($key);
    }
    
    static function getView($postId){
        if (!$postId) {
            return false;
        }
        
        //$cache = \Lib\Registry::get('cache');
        //$key = "post_views_{$postId}";
        //$result = $cache->load($key, 3000);
        //if (!$result) {
            try {
                $sql = "select views
            from post_views
            where post_id = :post_id
            limit 1";
                $b = \Lib\Database::getConnection()->prepare($sql);
                $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
                $b->execute();
                $result = $b->fetch(\PDO::FETCH_OBJ);
            } catch (Exception $e) {
                echo $e->getMessage();
                return 0;
            }
            //$cache->save($result, $key);
        //}
        
        return is_object($result) ? $result->views : 0;
    }
    
    static function getTags($postId){
        if (!$postId) {
            return false;
        }
        
        //$cache = \Lib\Registry::get('cache');
        //$key = "post_tags_{$postId}";
        //$result = $cache->load($key, 3000);
        //if (!$result) {
            try {
                $sql = "select t.tag
            from post_tags pt
            join tags t on t.id = pt.tag_id
            where pt.post_id = :post_id";
                $b = \Lib\Database::getConnection()->prepare($sql);
                $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
                $b->execute();
                $result = $b->fetchAll(\PDO::FETCH_ASSOC);
            } catch (Exception $e) {
                echo $e->getMessage();
                return array();
            }
            //$cache->save($result, $key);
        //}
        
        return is_array($result) ? $result: array();
    }
    
    static function getAllTags(){
        //$cache = \Lib\Registry::get('cache');
        //$key = "tagsForMenu";
        //$result = $cache->load($key, 3000);
        //if (!$result) {
            try {
                $sql = "select t.tag, count(pt.id) as count
            from post_tags pt
            join tags t on t.id = pt.tag_id
            group by pt.tag_id
            order by count desc";
                $b = \Lib\Database::getConnection()->prepare($sql);
                $b->execute();
                $result = $b->fetchAll(\PDO::FETCH_ASSOC);
            } catch (Exception $e) {
                echo $e->getMessage();
                return array();
            }
            //$cache->save($result, $key);
        //}
        return is_array($result) ? $result: array();
    }
    
    static function getTagsForMenu(){
        //$cache = \Lib\Registry::get('cache');
        //$key = "tagsForMenu";
        //$result = $cache->load($key, 3000);
        //if (!$result) {
            try {
                $sql = "select t.tag, count(pt.id) as count
            from post_tags pt
            join tags t on t.id = pt.tag_id
            group by pt.tag_id
            order by count desc
            limit 10";
                $b = \Lib\Database::getConnection()->prepare($sql);
                $b->execute();
                $result = $b->fetchAll(\PDO::FETCH_ASSOC);
            } catch (Exception $e) {
                echo $e->getMessage();
                return array();
            }
            //$cache->save($result, $key);
        //}
        return is_array($result) ? $result: array();
    }
    
    static function setTags($postId, $tags){
        if (!$postId || !is_array($tags)) {
            return false;
        }
        
        $cache = \Lib\Registry::get('cache');
        
        try {
        $conn = \Lib\Database::getConnection();   
        $conn->beginTransaction();
        $sql = "delete pt.*
            from post_tags pt
            where post_id = :post_id";
            $b = $conn->prepare($sql);
            $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
            $b->execute();
            
            
            foreach ($tags as $tag) {
                $tag = strtolower($tag);
                if(empty($tag)){
                    continue;
                }
                $key = "postsTag_{$tag}_0_20";
                $cache->delete($key);
                $sql = "insert
            into tags(tag)
            values(:tag)
            ON DUPLICATE KEY UPDATE tag = :tag";
                $b = $conn->prepare($sql);
                $b->bindValue("tag", $tag, \PDO::PARAM_STR);
                $b->execute();
                
                $sql = "select id
            from tags
            where tag = :tag";
                $b = $conn->prepare($sql);
                $b->bindValue("tag", $tag, \PDO::PARAM_STR);
                $b->execute();
                $tag_id = $b->fetch(\PDO::FETCH_ASSOC);
                
                $sql = "insert
            into post_tags(post_id, tag_id)
            values(:post_id, :tag_id)
            ON DUPLICATE KEY UPDATE tag_id = :tag_id";
                $b = $conn->prepare($sql);
                $b->bindValue("post_id", $postId, \PDO::PARAM_INT);
                $b->bindValue("tag_id", $tag_id['id'], \PDO::PARAM_INT);
                $b->execute();
            }
            $conn->commit();
        } catch (Exception $e) {
            echo $e->getMessage();
            $conn->rollBack();
            return false;
        }

        //$key = "post_tags_{$postId}";
        //$cache->delete($key);
    }
}

?>
